package com.zto.mybatis.test;

import org.junit.Test;

import java.io.FileNotFoundException;
import java.io.PrintStream;

/**
 * 生成多个sql语句
 *
 * @author sch
 */
public class GenerateTableSql {

    @Test
    public void memberInfo() {
        String sql = "\n" +
                "drop table if exists member_info_%04d;\n" +
                "create table member_info_%04d\n" +
                "(\n" +
                "id bigint unsigned auto_increment comment '主键id' primary key,\n" +
                "member_id varchar(32) default '' not null comment '会员编号',\n" +
                "user_id varchar(50) default '' not null comment '用户编号',\n" +
                "biz_id varchar(50) default '' not null comment '业务线id',\n" +
                "mobile varchar(15) default '' not null comment '手机号',\n" +
                "member_type varchar(50) default '' not null comment '会员类型',\n" +
                "nick_name varchar(100) default '' not null comment '会员昵称',\n" +
                "channel varchar(50) default '' not null comment '渠道',\n" +
                "terminal varchar(50) default '' not null comment '终端',\n" +
                "member_portrait varchar(50) default '' not null comment '会员头像(文件服务地址)',\n" +
                "note_name varchar(50) default '' not null comment '备注名',\n" +
                "registration_time datetime default CURRENT_TIMESTAMP not null comment '注册时间',\n" +
                "status tinyint default 0 not null comment '会员状态(1:启用, 2:停用,3:已注销)',\n" +
                "gmt_created datetime default CURRENT_TIMESTAMP not null comment '创建时间',\n" +
                "creator varchar(20) default '' not null comment '创建人',\n" +
                "gmt_modified datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '修改时间',\n" +
                "modifier varchar(20) default '' not null comment '修改人',\n" +
                "version int default 1 not null comment '版本号',\n" +
                "is_deleted tinyint(1) default 0 not null comment '是否删除',\n" +
                "KEY `idx_member_id` (`member_id`)\n" +
                ") comment '会员基础信息表';";

        generate(sql, 4, "member_info_ddl");
    }

    @Test
    public void memberGrowthInfo() {
        String sql = "create table member_growth_info_%04d\n" +
                "(\n" +
                "id bigint unsigned auto_increment comment '主键id' primary key,\n" +
                "member_id varchar(32) default '' not null comment '会员编号',\n" +
                "growth_value int default 0 not null comment '总成长值',\n" +
                "current_growth_value int default 0 not null comment '等级周期成长值',\n" +
                "grade_id varchar(32) default '' not null comment '等级id',\n" +
                "grade_name varchar(50) default '' not null comment '等级名称',\n" +
                "grade_start_time datetime default CURRENT_TIMESTAMP not null comment '开始时间',\n" +
                "grade_end_time datetime default CURRENT_TIMESTAMP not null comment '结束时间',\n" +
                "gmt_created datetime default CURRENT_TIMESTAMP not null comment '创建时间',\n" +
                "creator varchar(20) default '' not null comment '创建人',\n" +
                "gmt_modified datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '修改时间',\n" +
                "modifier varchar(20) default '' not null comment '修改人',\n" +
                "version int default 1 not null comment '版本号',\n" +
                "is_deleted tinyint(1) default 0 not null comment '是否删除',\n" +
                "KEY `idx_member_id` (`member_id`)\n" +
                ") comment '会员成长信息表';";

        generate(sql, 4, "member_growth_info_ddl");
    }

    @Test
    public void memberInfoTable1() {
        String sql = "create table member_growth_change_flow\n" +
                "(\n" +
                "id bigint unsigned auto_increment comment '主键id' primary key,\n" +
                "member_id varchar(32) default '' not null comment '会员id',\n" +
                "growth_flow_id varchar(32) default '' not null comment '成长值流水表id',\n" +
                "change_type tinyint default 0 not null comment '变更类型(1:增加成长值, 2:降低成长值)',\n" +
                "channel varchar(50) default '' not null comment '渠道',\n" +
                "change_growth_value int default 0 not null comment '变更的成长值',\n" +
                "before_growth_value int default 0 not null comment '变更前成长值',\n" +
                "after_growth_value int default 0 not null comment '变更后成长值',\n" +
                "change_time datetime default CURRENT_TIMESTAMP not null comment '变更时间',\n" +
                "reason varchar(200) default '' not null comment '原因',\n" +
                "factor_type varchar(20) default '' not null comment '类型',\n" +
                "sub_type varchar(20) default '' not null comment '子类型',\n" +
                "gmt_created datetime default CURRENT_TIMESTAMP not null comment '创建时间',\n" +
                "creator varchar(20) default '' not null comment '创建人',\n" +
                "gmt_modified datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '修改时间',\n" +
                "modifier varchar(20) default '' not null comment '修改人',\n" +
                "is_deleted tinyint(1) default 0 not null comment '是否删除',\n" +
                "KEY `idx_member_id` (`member_id`)\n" +
                ") comment '会员成长值流水表';";
        generate(sql, 4, "member_user_relation_ddl");
    }

    @Test
    public void memberInfoTable2() {
        String sql = "create table `member_detail_info_%04d`\n" +
                "(\n" +
                "id bigint unsigned auto_increment comment '主键id' primary key,\n" +
                "member_id varchar(32) default '' not null comment '会员编号',\n" +
                "gender varchar(50) default '' not null comment '性别',\n" +
                "birthday varchar(50) default '' not null comment '生日',\n" +
                "province_id int default 0 not null comment '省id',\n" +
                "province_name varchar(50) default '' not null comment '省名称',\n" +
                "city_id int default 0 not null comment '市id',\n" +
                "city_name varchar(50) default '' not null comment '市名称',\n" +
                "district_id int default 0 not null comment '区id',\n" +
                "district_name varchar(50) default '' not null comment '区名称',\n" +
                "address varchar(100) default '' not null comment '地址',\n" +
                "education tinyint default 0 not null comment '学历()',\n" +
                "`describe` varchar(100) default '' not null comment '描述',\n" +
                "gmt_created datetime default CURRENT_TIMESTAMP not null comment '创建时间',\n" +
                "creator varchar(20) default '' not null comment '创建人',\n" +
                "gmt_modified datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '修改时间',\n" +
                "modifier varchar(20) default '' not null comment '修改人',\n" +
                "version int default 1 not null comment '版本号',\n" +
                "is_deleted tinyint(1) default 0 not null comment '是否删除',\n" +
                "KEY `idx_member_id` (`member_id`)\n" +
                ") comment '会员详细信息表';\n" +
                "\n";
        generate(sql, 4, "member_detail_info_ddl");
    }

    @Test
    public void member_growth_change_flow() {
        String sql = "create table member_growth_change_flow_%04d\n" +
                "(\n" +
                "id bigint unsigned auto_increment comment '主键id' primary key,\n" +
                "member_id varchar(32) default '' not null comment '会员id',\n" +
                "growth_flow_id varchar(32) default '' not null comment '成长值流水表id',\n" +
                "change_type tinyint default 0 not null comment '变更类型(1:增加成长值, 2:降低成长值)',\n" +
                "channel varchar(50) default '' not null comment '渠道',\n" +
                "change_growth_value int default 0 not null comment '变更的成长值',\n" +
                "before_growth_value int default 0 not null comment '变更前成长值',\n" +
                "after_growth_value int default 0 not null comment '变更后成长值',\n" +
                "change_time datetime default CURRENT_TIMESTAMP not null comment '变更时间',\n" +
                "reason varchar(200) default '' not null comment '原因',\n" +
                "factor_type varchar(20) default '' not null comment '类型',\n" +
                "sub_type varchar(20) default '' not null comment '子类型',\n" +
                "gmt_created datetime default CURRENT_TIMESTAMP not null comment '创建时间',\n" +
                "creator varchar(20) default '' not null comment '创建人',\n" +
                "gmt_modified datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '修改时间',\n" +
                "modifier varchar(20) default '' not null comment '修改人',\n" +
                "is_deleted tinyint(1) default 0 not null comment '是否删除',\n" +
                "KEY `idx_member_id` (`member_id`)\n" +
                ") comment '会员成长值流水表';" +
                "\n";
        generate(sql, 8, "member_growth_change_flow_ddl");
    }

    @Test
    public void member_grade_change_flow() {
        String sql = "drop table if exists member_grade_change_flow_%04d;\n" +
                "create table member_grade_change_flow_%04d\n" +
                "(\n" +
                "id bigint unsigned auto_increment comment '主键id' primary key,\n" +
                "member_id varchar(32) default '' not null comment '会员卡号',\n" +
                "grade_flow_id varchar(32) default '' not null comment '等级变更流水表id',\n" +
                "growth_flow_id varchar(32) default '' not null comment '成长值流水表id',\n" +
                "before_change_grade_id varchar(32) default '' not null comment '变更前等级id',\n" +
                "after_change_grade_id varchar(32) default '' not null comment '变更后等级id',\n" +
                "change_type tinyint default 0 not null comment '变更类型(1:增加等级, 2:降低等级)',\n" +
                "change_time datetime default CURRENT_TIMESTAMP not null comment '变更时间',\n" +
                "reason varchar(200) default '' not null comment '原因',\n" +
                "gmt_created datetime default CURRENT_TIMESTAMP not null comment '创建时间',\n" +
                "creator varchar(20) default '' not null comment '创建人',\n" +
                "gmt_modified datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '修改时间',\n" +
                "modifier varchar(20) default '' not null comment '修改人',\n" +
                "is_deleted tinyint(1) default 0 not null comment '是否删除',\n" +
                "KEY `idx_member_id` (`member_id`)\n" +
                ") comment '会员等级变更流水';  ";
        generate(sql, 8, "member_grade_change_flow_ddl");
    }


    public void generate(String sql, int n, String fileName) {


        PrintStream ps = null;
        try {
            ps = new PrintStream("/Users/sch/mydocument/temp/" + fileName + ".sql");

            System.setOut(ps);

            for (int i = 0; i < n; i++) {

                System.out.println(String.format(sql, i, i));

            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }


    }


}
